package com.ltsznh.android.familybooks.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.ltsznh.android.familybooks.R;

import java.io.BufferedReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

/**
 * Created by Administrator on 13-8-27.
 *
 */
public class SQLiteHelper extends SQLiteOpenHelper {
    protected Context ctx;
    protected SQLiteDatabase initDb;
    public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.ctx = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        initDb = db;
        Log.v("TTTT","onCreateDb");
        String sql = "CREATE  TABLE addressbook (_id INTEGER PRIMARY KEY , name VARCHAR, phone VARCHAR)";
        //db.execSQL(sql);

        //sys_users
        sql = "CREATE TABLE if not exists sys_users ( " +
                "userCode VARCHAR PRIMARY KEY NOT NULL UNIQUE, " +
                "userName VARCHAR NOT NULL, phoneNumber VARCHAR, " +
                "userPwd VARCHAR, " +
                "isDefault CHAR NOT NULL DEFAULT ( 'N' ), " +
                "isEnabled CHAR NOT NULL DEFAULT ( 'Y' ))";
        //db.execSQL(sql);

        //inv_blotters
        sql = "CREATE TABLE if not exists inv_blotters ( " +
                "id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL UNIQUE, " +
                "invDate DATE NOT NULL, " +
                "entryDate DATE DEFAULT ( datetime( 'now', 'localtime' )  ), " +
                "entryTime DATETIME DEFAULT ( datetime( 'now', 'localtime' )  ), " +
                "invPicture BLOB, " +
                "yuan INT, " +
                "invType VARCHAR, " +
                "invCategory VARCHAR, " +
                "invCategory2 VARCHAR, " +
                "accountCategory VARCHAR, " +
                "account VARCHAR, " +
                "place VARCHAR, " +
                "placeTime DATETIME DEFAULT ( datetime( 'now', 'localtime' )  ), " +
                "longitude VARCHAR, " +
                "latitude VARCHAR, " +
                "accuracy VARCHAR, " +
                "remark VARCHAR, " +
                "isInvalid CHAR NOT NULL DEFAULT ( 'N' ), " +
                "correlationSeq INTEGER )";
        db.execSQL(sql);

        //inv_category
        sql = "CREATE TABLE if not exists inv_category ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, " +
                "invType VARCHAR NOT NULL, " +
                "invCategory VARCHAR NOT NULL, " +
                "invCategory2 VARCHAR NOT NULL, " +
                "displayOrder INT, " +
                "isEnabled    CHAR    DEFAULT ( 'Y' ) )";
        db.execSQL(sql);

        //inv_account
        sql = "CREATE TABLE if not exists inv_account ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, " +
                "accountCategory VARCHAR NOT NULL, " +
                "account VARCHAR NOT NULL, " +
                "displayOrder    INT, " +
                "repaymentDate   DATE, " +
                "isEnabled       CHAR    DEFAULT ( 'Y' ) )";
        db.execSQL(sql);

        try {
            this.initData();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    public void initData() throws IOException {
        InputStream myInput = ctx.getResources().openRawResource(R.raw.initdata);
        InputStreamReader reader = new InputStreamReader(myInput);
        BufferedReader breader = new BufferedReader(reader);

        // Path to the just created empty db
        String outFileName = "/data/data/com.ltsznh.android.familybooks/databases/" + dbo.fbSQLiteName,
                str;

        // Open the empty db as the output stream
        FileWriter myOutput = null;

        myOutput = new FileWriter(outFileName, true);
        //exec your SQL line by line.
        while ((str = breader.readLine()) != null) initDb.execSQL(str);
        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

//    public void initDataValue() throws IOException {
//        InputStream myInput = ctx.getResources().openRawResource(R.raw.data);
//        InputStreamReader reader = new InputStreamReader(myInput);
//        BufferedReader breader = new BufferedReader(reader);
//
//        // Path to the just created empty db
//        String outFileName = "/data/data/com.ltsznh.android.familybooks/databases/" + dbo.fbSQLiteName,
//                str;
//
//        // Open the empty db as the output stream
//        FileWriter myOutput = null;
//
//        myOutput = new FileWriter(outFileName, true);
//        //exec your SQL line by line.
//        while ((str = breader.readLine()) != null) initDb.execSQL(str);
//        // Close the streams
//        myOutput.flush();
//        myOutput.close();
//        myInput.close();
//    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
//        initDb = sqLiteDatabase;
//        if(oldVersion == 1 && newVersion == 2){
//            try {
//                initDataValue();
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//        onCreate(getWritableDatabase());
//        Log.v("TTTT","onUpgrade" + oldVersion + " " + newVersion);

    }
}
